package com.csi.okr.mobile.self.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.transaction.Transactional;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.csi.okr.common.util.JsonUtil;
import com.csi.okr.common.util.Pagination;
import com.csi.okr.mobile.common.dao.TOkrKeyResultsDao;
import com.csi.okr.mobile.common.dao.TOkrObjectivesDao;
import com.csi.okr.mobile.common.dao.TOkrReviewDao;
import com.csi.okr.mobile.common.dao.TOkrReviewDetailDao;
import com.csi.okr.mobile.common.dao.TOkrScoreDao;
import com.csi.okr.mobile.common.dao.TOkrVersionKeyresultsDao;
import com.csi.okr.mobile.common.dao.TOkrVersionObjectivesDao;
import com.csi.okr.mobile.common.dao.VOkrOrgDao;
import com.csi.okr.mobile.self.service.SelfService;

@Service
@Transactional
public class SelfServiceImpl implements SelfService {

	@Resource
	private TOkrObjectivesDao objDao;
	@Resource
	private TOkrVersionObjectivesDao versionObjDao;
	@Resource
	private TOkrVersionKeyresultsDao versionkeyDao;
	@Resource
	private TOkrKeyResultsDao keyResultDao;
	@Resource
	private TOkrReviewDao reviewDao;
	@Resource
	private TOkrReviewDetailDao reviewDetlDao;
	@Resource
	private	TOkrScoreDao scoreDao;
	@Resource
	private VOkrOrgDao orgDao;
	
	@Override
	public Object getOkrByEmplCode(int pageIndex,int pageSize,String orgCode,String emplCode,String month,
			String year,String quarter,String state,String odefinition) {

		String sql = "SELECT T1.*,T2.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"'";
		
		if(orgCode != null && !"".equals(orgCode)){
			sql += " AND T1.OWNER_DEPTCODE = '"+orgCode+"'";
		}
		
		if(month != null && !"".equals(month) && !"0".equals(month)){
			sql += " AND T1.O_MONTH = '"+month+"'";
		}
		
		if (quarter != null && !"".equals(quarter) && !"Q0".equals(quarter)) {
			sql += " AND T1.O_QUARTER = '"+quarter+"'";
		}
			
		if (state != null && !"".equals(state)) {
			//第一次进入
			if(state.equals("query")){
				//暂无
			}else if(state.equals("oauth")){
				sql += " AND T1.O_STATE != 'S5'";
			}else if("0".equals(state)){
				//暂无
			}else{
				sql += " AND T1.O_STATE = '"+state+"'";
			}
		}
		
		if (odefinition != null && !"".equals(odefinition)) {
			sql += " AND T1.O_DEFINITION = '"+odefinition+"'";
		} 
		
		if (year != null  && !"".equals(year) && !"请选择".equals(year)) {
			if(Integer.parseInt(year) != 0){
				sql += " AND T1.O_YEAR = " + Integer.parseInt(year);
			}
		}
		
		sql += " ORDER BY T1.O_YEAR DESC,T1.O_QUARTER DESC,T1.O_MONTH DESC,T1.O_CODE ASC";
		
		Pagination<Map<String, Object>> data = objDao.findSqlPagination(sql, pageIndex, pageSize);
		
		return JsonUtil.parseJson(data);
	}
	

	@Override
	public Object getOrgTree(String emplCode) {
		
		String sql = "SELECT DISTINCT T1.OWNER_DEPTCODE,T1.OWNER_DEPTNAME FROM T_OKR_OBJECTIVES T1 WHERE T1.PERSON_LIABLE_CODE = '"+emplCode+"'";
		List<Map<String, Object>> data = objDao.excuteSql(sql);
		//序列化
		String format = JSON.toJSONString(data);
		//正则替换掉原有的key 为了调用前端组织树的js方法
		format = format.replaceAll("OWNER_DEPTNAME", "name").replaceAll("OWNER_DEPTCODE", "url");
		return format;
	}
	
	@Override
	public Object getVersionInfo(String uniqueCode) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		List<Map<String, Object>> map_obj = objDao.excuteSql("SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'");
		
		if(map_obj == null ||map_obj.size() == 0){
			return -1;
		}
		
		Map<String, Object> obj = map_obj.get(0);

		if(obj == null){
			return -1;
		}
		
		//根据O主键查询review
		String sql = "SELECT T1.* FROM T_OKR_VERSION_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'";
		
		List<Map<String, Object>> versions = versionObjDao.excuteSql(sql);
		
		data.put("obj", obj);
		data.put("version", versions);
		
		return JsonUtil.parseJson(data);
	}

	@Override
	public Object getVersionDetailInfo(String versionUuid) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		List<Map<String, Object>> map_obj = objDao.excuteSql("SELECT T1.* FROM T_OKR_VERSION_OBJECTIVES T1 LEFT JOIN T_OKR_OBJECTIVES T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE WHERE T1.O_VERSION_UUID = '"+versionUuid+"'");
		
		if(map_obj == null ||map_obj.size() == 0){
			return -1;
		}
		
		Map<String, Object> obj = map_obj.get(0);

		if(obj == null){
			return -1;
		}
		
		//根据O主键查询review
		String sql = "SELECT T1.*,T2.KR_SCORE_SUPERIOR FROM T_OKR_VERSION_KEYRESULTS T1 LEFT JOIN T_OKR_SCORE_DETAIL_KEYRESULTS T2 ON  T1.KR_UNIQUE_ID_CODE = T2.KR_UNIQUE_ID_CODE WHERE T1.O_VERSION_UUID = '"+versionUuid+"'";
		
		List<Map<String, Object>> versionds = versionObjDao.excuteSql(sql);
		
		data.put("obj", obj);
		data.put("versiond", versionds);
		
		return JsonUtil.parseJson(data);
	}

	@Override
	public Object getScoreInfo(String uniqueCode) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		String sql_score = "SELECT T1.*,T3.REPORT_FILE_NAME,O_SCORE_REPORT_UUID FROM T_OKR_SCORE T1 LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T2 ON T1.O_SCORE_UUID = T2.O_SCORE_UUID LEFT JOIN T_OKR_SCORE_REPORT T3 ON T1.O_SCORE_UUID = T3.O_SCORE_UUID WHERE T2.O_UNIQUE_ID_CODE = '"+uniqueCode+"'";
		List<Map<String, Object>> map_score = objDao.excuteSql(sql_score);
		
		if(map_score == null ||map_score.size() == 0){
			return -1;
		}
		
		Map<String, Object> obj = map_score.get(0);
		
		if(obj == null){
			return -1;
		}
		
		String sql = "SELECT T1.*,T2.O_CODE FROM T_OKR_SCORE_DETAIL_OBJECTIVES T1 LEFT JOIN T_OKR_OBJECTIVES T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE WHERE T1.O_SCORE_UUID = '"+obj.get("O_SCORE_UUID")+"' ORDER BY T2.O_CODE ASC";
		
		List<Map<String, Object>> scores = scoreDao.excuteSql(sql);
		
		data.put("score", obj);
		data.put("scores", scores);
		
		return JsonUtil.parseJson(data);
	}

	@Override
	public Object getLinkOkr(String uniqueCode) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		String sql = "SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'";
		Map<String, Object> self_obj = objDao.getUniqueBySql(sql);
		
		data.put("self", self_obj);
		data.put("parent", null);
		
		//用户编号
		String PERSON_LIABLE_CODE = (String) self_obj.get("PERSON_LIABLE_CODE");
		//关联OKR类型
		String PARENT_OKR_RELATION_TYPE = (String) self_obj.get("PARENT_OKR_RELATION_TYPE");
		//关联OKR唯一标识
		String PARENT_OKR_UNIQUE_ID_CODE = (String) self_obj.get("PARENT_OKR_UNIQUE_ID_CODE");
		
		System.out.println(JSONObject.toJSONString(self_obj));
		
		//如果有关联数据
		if(PARENT_OKR_RELATION_TYPE != null && !"".equals(PARENT_OKR_RELATION_TYPE)){
			String sql_parent = "";
			//关联O
			if(PARENT_OKR_RELATION_TYPE.equals("O")){
				//查询关联的上级OKR
				sql_parent = "SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+PARENT_OKR_UNIQUE_ID_CODE+"'";
			}
			//关联KR
			else if(PARENT_OKR_RELATION_TYPE.equals("KR")){
				//查询关联的上级OKR
				sql_parent = "SELECT T2.* FROM T_OKR_KEYRESULTS T1 LEFT JOIN T_OKR_OBJECTIVES T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE WHERE T1.KR_UNIQUE_ID_CODE = '"+PARENT_OKR_UNIQUE_ID_CODE+"'";;
			}
			
			Map<String, Object> pareng_obj = objDao.getUniqueBySql(sql_parent);
			
			if(pareng_obj != null){
				
				//所属组织
				String OWNER_DEPTCODE = (String) pareng_obj.get("OWNER_DEPTCODE");
				
				//是否全公司透明
				Integer IS_VISIBLE_OVERALL = Integer.valueOf(pareng_obj.get("IS_VISIBLE_OVERALL").toString());
				if(IS_VISIBLE_OVERALL != null && IS_VISIBLE_OVERALL.equals(1)){
					data.put("parent", pareng_obj);
				}else{
					//查询此用户组织信息
					String sql_org = "SELECT * FROM V_OKR_EMPL_ORG V1 WHERE V1.EMPL_CODE = '"+PERSON_LIABLE_CODE+"' AND V1.ORG_CODE = '"+OWNER_DEPTCODE+"'";
					Map<String, Object> V_ORG_EMPL = objDao.getUniqueBySql(sql_org);
					
					if(V_ORG_EMPL != null){
						
						Integer isLeader = (Integer) V_ORG_EMPL.get("IS_LEADER");
						
						//是否是负责人
						if(isLeader != null && isLeader.equals(1)){
							
							Integer IS_VISIBLE_SUPERIOR =(Integer)V_ORG_EMPL.get("IS_VISIBLE_SUPERIOR");
							
							if(IS_VISIBLE_SUPERIOR != null && IS_VISIBLE_SUPERIOR.equals(1)){
								data.put("parent", pareng_obj);
							}
						}
						//不是负责人
						else if(isLeader == null || isLeader.equals(0)){
							
							String P_PERSON_LIABLE_CODE = (String) pareng_obj.get("PERSON_LIABLE_CODE");
							//查询上级OKR责任人组织信息
							String sql_pareng_org_empl = "SELECT * FROM V_OKR_EMPL_ORG V1 WHERE V1.EMPL_CODE = '"+P_PERSON_LIABLE_CODE+"' AND V1.ORG_CODE = '"+OWNER_DEPTCODE+"'";
							Map<String, Object> P_V_ORG_EMPL = objDao.getUniqueBySql(sql_pareng_org_empl);
							
							if(P_V_ORG_EMPL != null){
								//上级OKR责任人是否是负责人
								Integer pIsLeader = (Integer) P_V_ORG_EMPL.get("IS_LEADER");
								if(pIsLeader != null && pIsLeader.equals(1)){
									Integer IS_VISIBLE_SUBORDINATE = (Integer)V_ORG_EMPL.get("IS_VISIBLE_SUBORDINATE");
									//是否下级可见
									if(IS_VISIBLE_SUBORDINATE != null && IS_VISIBLE_SUBORDINATE.equals(1)){
										data.put("parent", pareng_obj);
									}
								}
							}
						}else{
							//是否指定人可见
							Integer IS_VISIBLE_DESIGNATED =(Integer)V_ORG_EMPL.get("IS_VISIBLE_DESIGNATED");
							// 如是
							if(IS_VISIBLE_DESIGNATED != null && IS_VISIBLE_DESIGNATED.equals(1)){
								//查询是否有查看权限
								String sql_show = "SELECT * FROM T_OKR_DESIGNATED_VISIBLE T1 WHERE T1.O_UNIQUE_ID_CODE = '"+PARENT_OKR_UNIQUE_ID_CODE+"' AND T1.DESIGNATEDE_PERSON_CODE = '"+PERSON_LIABLE_CODE+"'";
								Map<String, Object> T_OKR_DESIGNATED_VISIBLE = objDao.getUniqueBySql(sql_show);
								if(T_OKR_DESIGNATED_VISIBLE != null){
									data.put("parent", pareng_obj);
								}
							}
						}
					}
					
				}
			}
			
		}
		
		//查询子集
		String sql_child ="SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.PARENT_OKR_RELATION_TYPE = 'O' AND T1.PARENT_OKR_UNIQUE_ID_CODE = '"+uniqueCode+"' UNION ALL SELECT T2.* FROM T_OKR_KEYRESULTS T1 LEFT JOIN T_OKR_OBJECTIVES T2 ON  T1.KR_UNIQUE_ID_CODE = T2.PARENT_OKR_UNIQUE_ID_CODE WHERE T2.PARENT_OKR_RELATION_TYPE = 'KR' AND T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'";
		List<Map<String, Object>> child = objDao.excuteSql(sql_child);
		data.put("child", child);
		
		return JsonUtil.parseJson(data);
	}

	@Override
	public Object getReviewInfo(String uniqueCode) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		List<Map<String, Object>> map_obj = objDao.excuteSql("SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'");
		
		if(map_obj == null ||map_obj.size() == 0){
			return -1;
		}
		
		Map<String, Object> obj = map_obj.get(0);

		if(obj == null){
			return -1;
		}
		
		//根据O主键查询review
		String sql = "SELECT DISTINCT T1.* FROM T_OKR_REVIEW T1 LEFT JOIN T_OKR_REVIEW_DETAIL T2 ON T1.OKR_REVIEW_UUID = T2.OKR_REVIEW_UUID WHERE T2.O_UNIQUE_ID_CODE = '"+uniqueCode+"'";
		
		List<Map<String, Object>> reviews = reviewDao.excuteSql(sql);
		
		data.put("obj", obj);
		data.put("review", reviews);
		
		return JsonUtil.parseJson(data);
	}
	
	@Override
	public Object getReviewDetailInfo(String uniqueCode,String reviewUuid) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		List<Map<String, Object>> map_obj = objDao.excuteSql("SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'");
		
		if(map_obj == null ||map_obj.size() == 0){
			return -1;
		}
		
		Map<String, Object> obj = map_obj.get(0);

		if(obj == null){
			return -1;
		}
		//根据O主键查询review
		String sql = "SELECT T3.CODE_NAME,T2.KR_CODE,T2.KR_DEFINITION,T2.ACTION_PLAN,T1.* FROM T_OKR_REVIEW_DETAIL T1 LEFT JOIN T_OKR_KEYRESULTS T2 ON T1.KR_UNIQUE_ID_CODE = T2.KR_UNIQUE_ID_CODE LEFT JOIN T_OKR_BASECODE_CODE T3 ON T2.KR_CODE = T3.CODE_NO WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"' ORDER BY T2.KR_CODE";
		
		List<Map<String, Object>> reviewd = reviewDao.excuteSql(sql);
		
		data.put("obj", obj);
		data.put("reviewd", reviewd);
		
		return JsonUtil.parseJson(data);
	}
	
	@Override
	public Object getScoreDetailInfo(String uniqueCode) {
		Map<String, Object> data = new HashMap<String, Object>();
		
		List<Map<String, Object>> map_obj = objDao.excuteSql("SELECT * FROM T_OKR_OBJECTIVES T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'");
		
		if(map_obj == null ||map_obj.size() == 0){
			return -1;
		}
		
		Map<String, Object> obj = map_obj.get(0);
		
		if(obj == null){
			return -1;
		}
		
		String sql = "SELECT * FROM T_OKR_SCORE_DETAIL_KEYRESULTS T1 WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"'";
		
		List<Map<String, Object>> krScores = reviewDao.excuteSql(sql);
		
		data.put("obj", obj);
		data.put("krScores", krScores);
		
		return JsonUtil.parseJson(data);
		
	}
	
	@Override
	public Object getkrviewInfo(String uniqueCode) {
		
		Map<String, Object> data = new HashMap<String, Object>();
		
		Map<String, Object> obj = objDao.getUniqueBySql("SELECT T1.*,T2.O_SCORE_SUPERIOR FROM T_OKR_OBJECTIVES T1 LEFT JOIN T_OKR_SCORE_DETAIL_OBJECTIVES T2 ON T1.O_UNIQUE_ID_CODE = T2.O_UNIQUE_ID_CODE WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"' ");
		
		String sql = "SELECT T1.*,T2.KR_SCORE_SUPERIOR,T3.CODE_NAME FROM T_OKR_KEYRESULTS T1 LEFT JOIN T_OKR_SCORE_DETAIL_KEYRESULTS T2 ON T1.KR_UNIQUE_ID_CODE = T2.KR_UNIQUE_ID_CODE LEFT JOIN T_OKR_BASECODE_CODE T3 ON T1.KR_CODE = T3.CODE_NO WHERE T1.O_UNIQUE_ID_CODE = '"+uniqueCode+"' ORDER BY T1.KR_CODE ASC";
		
		List<Map<String, Object>> krs = reviewDao.excuteSql(sql);
		
		data.put("obj", obj);
		data.put("krs", krs);
		
		return JsonUtil.parseJson(data);
	}


	@Override
	public Map<String, Object> getScoreReportFile(String uuid) {
		
		return scoreDao.getUniqueBySql("SELECT * FROM T_OKR_SCORE_REPORT WHERE O_SCORE_REPORT_UUID = '"+uuid+"'");
	}


}
